
clear
  set more off
foreach x in bog1 bog2 bog3 bog4 bog5 bog6 bog7 bog8 bog9 bog10 bog11 bog12 bog13 ///
 bog14 bog15 bog16 bog17 bog18 bog19 bog20 bog21 bog22 cali1 cali2 cali4 cali5 ibague1 iba2 iba3 palmira ///
 pasto pereria tunja med2 med1 med3 med4 med5  arm2 arme flor  neiva mani buga  buca1 buca2 villa bar1 bar2 popa {
 import delimited FirstPrisonData`x'.csv
 rename var_1 juz_ep
 rename var_2 city
  rename var_3 year_jep
   rename var_4 CODANE
   rename var_5 corporacion
    rename var_6 sala
	 rename var_7 despacho
	  rename var_8 year_j
	   rename var_9 n_radicado
	    rename var_10 recurso 
		 rename var_11 sentencia_ant
		  rename var_12 juz_falla1
		   rename var_13 date_fallo1
		    rename var_14 date_ejecf1
			rename var_16 juz_falla2
		   rename var_17 date_fallo2
		    rename var_18 date_ejecf2
			 rename var_20 juz_falla3
		   rename var_21 date_fallo3
		    rename var_22 date_ejecf3
			
			destring id, replace
			destring juz_ep, replace
			destring CODANE, replace
			destring corporacion, replace
			destring sala, replace
			destring despacho, replace
			destring year_j, replace
			destring n_radicado, replace
			destring recurso, replace
			drop var_23 var_24
			
keep id juz_ep city year_jep CODANE corporacion sala despacho year_j n_radicado ///
recurso sentencia_ant juz_falla1 date_fallo1 date_ejecf1 juz_falla2 date_fallo2 date_ejecf2 juz_falla3 date_fallo3 date_ejecf3
			gen file=`x'
			tostring file, replace
			tostring id, replace
			tostring year_j, replace
			tostring juz_ep, replace
			tostring year_jep, replace
			tostring CODANE, replace
			tostring corporacion, replace
			tostring sala, replace
			tostring despacho, replace
			tostring n_radicado, replace
			tostring recurso, replace
			
			save "FirstPrisonData`x'.dta", replace
			clear
		  }
		  
	

		
  foreach x in og1 bog2 bog3 bog4 bog5 bog6 bog7 bog8 bog9 bog10 bog11 bog12 bog13 ///
 bog14 bog15 bog16 bog17 bog18 bog19 bog20 bog21 bog22 cali1 cali2 cali4 cali5 ibague1 iba2 iba3 palmira ///
 pasto pereria tunja med2 med1 med3 med4 med5  arm2 arme flor  neiva mani buga  buca1 buca2 villa bar1 bar2 popa     {
append using "FirstPrisonData`x'.dta"
														
	}
	
	* Cleaning
	set more off
	foreach var in  juz_ep year_jep CODANE corporacion sala despacho year_j n_radicado recurso  {
foreach x in A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Ñ Ãº Ã¡ Ã³ Ã± Ã¹ Ã² - .  / & ; :  $ ,  * Ã ( ) ndocumentado Ã° __  Ã _ indocumentadass iniudad a b c d e f g h i j k l m n o p q r s t u v w x y z{
replace `var' = subinstr(`var', "`x'", "", .)
}
replace `var' = subinstr(`var', " ", "", .)
destring `var', replace
}
	
generate byte non_numeric = indexnot( id, "0123456789")
drop if non_numeric
destring id, replace

egen judge=concat(CODANE corporacion sala despacho) if corporacion==31 | corporacion==32 |  corporacion==40
destring judge, replace
														 
		replace juz_falla1 = subinstr(juz_falla1, "JDO", "JUZGADO", .)
		replace juz_falla1 = subinstr(juz_falla1, "PM", "PENAL MUNICIPAL", .)
		replace juz_falla1 = subinstr(juz_falla1, "JUZG ", "JUZGADO ", .)
		replace juz_falla1 = subinstr(juz_falla1, "JUZGAD ", "JUZGADO ", .)
		replace juz_falla1 = subinstr(juz_falla1, "JUZGAADO", "JUZGADO", .)
		replace juz_falla1 = subinstr(juz_falla1, "MPAL", "MUNICIPAL", .)
replace juz_falla1 = subinstr(juz_falla1, "DE CMTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "DE CONOCIMIENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "DEL CTO", "DEL CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "BTA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "BOGOTA DC", "", .)
replace juz_falla1 = subinstr(juz_falla1, "BOGOTA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZGADOOADO", "JUZGADO", .)
replace juz_falla1 = subinstr(juz_falla1, "		", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "0JUZGADOOADO", "JUZGADO", .)
replace juz_falla1 = subinstr(juz_falla1, "CTO", "CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "PCUO", "PROMISCUO", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZF", "JUZGADO", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZD", "JUZGADO", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZGADOP ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZAGDO ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUXGADO ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZADO ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZ ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "J ", " JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUIZGADO ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUGADO ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUIZGADO ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZ ", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "CONOCIMIENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CON FUNCIONES DE", "", .)
replace juz_falla1 = subinstr(juz_falla1, "PENAL CIRCUITO", "PENAL DEL CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "PCIRCUITO", "PENAL DEL CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "P CIRCUITO", "PENAL DEL CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "|", "", .)
replace juz_falla1 = subinstr(juz_falla1, "P MUNICIPAL", "PENAL MUNICIPAL", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZGADO", "JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "PRIMERO", "1", .)
replace juz_falla1 = subinstr(juz_falla1, "001", "1", .)
replace juz_falla1 = subinstr(juz_falla1, " 01", " 1", .)
replace juz_falla1 = subinstr(juz_falla1, "1ERO", " 1", .)
replace juz_falla1 = subinstr(juz_falla1, "SEGUNDO", "2", .)
replace juz_falla1 = subinstr(juz_falla1, "002", "2", .)
replace juz_falla1 = subinstr(juz_falla1, " 02", "2", .)
replace juz_falla1 = subinstr(juz_falla1, "2DO", "2", .)
replace juz_falla1 = subinstr(juz_falla1, "TERCERO", "3", .)
replace juz_falla1 = subinstr(juz_falla1, "003", "3", .)
replace juz_falla1 = subinstr(juz_falla1, " 03", "3", .)
replace juz_falla1 = subinstr(juz_falla1, "3ERO", "3", .)
replace juz_falla1 = subinstr(juz_falla1, "CUARTO", "4", .)
replace juz_falla1 = subinstr(juz_falla1, "004", "4", .)
replace juz_falla1 = subinstr(juz_falla1, " 04", "4", .)
replace juz_falla1 = subinstr(juz_falla1, "4TO", "4", .)
replace juz_falla1 = subinstr(juz_falla1, "QUINTO", "5", .)
replace juz_falla1 = subinstr(juz_falla1, "005", "5", .)
replace juz_falla1 = subinstr(juz_falla1, " 05", "5", .)
replace juz_falla1 = subinstr(juz_falla1, "5TO", "5", .)
replace juz_falla1 = subinstr(juz_falla1, "SEXTO", "6", .)
replace juz_falla1 = subinstr(juz_falla1, "006", "6", .)
replace juz_falla1 = subinstr(juz_falla1, " 06", "6", .)
replace juz_falla1 = subinstr(juz_falla1, "6TO", "6", .)
replace juz_falla1 = subinstr(juz_falla1, "SEPTIMO", "7", .)
replace juz_falla1 = subinstr(juz_falla1, "007", "7", .)
replace juz_falla1 = subinstr(juz_falla1, " 07", "7", .)
replace juz_falla1 = subinstr(juz_falla1, "7MO", "7", .)
replace juz_falla1 = subinstr(juz_falla1, "OCTAVO", "8", .)
replace juz_falla1 = subinstr(juz_falla1, "008", "8", .)
replace juz_falla1 = subinstr(juz_falla1, " 08", "8", .)
replace juz_falla1 = subinstr(juz_falla1, "8VO", "8", .)
replace juz_falla1 = subinstr(juz_falla1, "NOVENO", "9", .)
replace juz_falla1 = subinstr(juz_falla1, "009", "9", .)
replace juz_falla1 = subinstr(juz_falla1, " 09", "9", .)
replace juz_falla1 = subinstr(juz_falla1, "9NO", "9", .)
replace juz_falla1 = subinstr(juz_falla1, "DECIMO", "10", .)
replace juz_falla1 = subinstr(juz_falla1, "ONCE", "11", .)
replace juz_falla1 = subinstr(juz_falla1, "DOCE", "12", .)
replace juz_falla1 = subinstr(juz_falla1, "TRECE", "13", .)
replace juz_falla1 = subinstr(juz_falla1, "CATORCE", "14", .)
replace juz_falla1 = subinstr(juz_falla1, "QUINCE", "15", .)
replace juz_falla1 = subinstr(juz_falla1, "DIEZ Y SEIS", "16", .)
replace juz_falla1 = subinstr(juz_falla1, "DIEZ Y SIETE", "17", .)
replace juz_falla1 = subinstr(juz_falla1, "DIEZ Y OCHO", "18", .)
replace juz_falla1 = subinstr(juz_falla1, "DIEZ Y NUEVE", "19", .)
replace juz_falla1 = subinstr(juz_falla1, "VEINTE", "20", .)
replace juz_falla1 = subinstr(juz_falla1, "JD ", " JUZGADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "CON FUNCION DE", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CON FUNCIÃN DE", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CIRUCUITO", "CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "MUNICIPAL.", "MUNICIPAL", .)
replace juz_falla1 = subinstr(juz_falla1, "CON FUNCIÃN", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CON FUNCION", "", .)
replace juz_falla1 = subinstr(juz_falla1, "FUNCION", "", .)  
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "MUNCIPAL", "MUNICIPAL", .)
replace juz_falla1 = subinstr(juz_falla1, "MUNICIPAL DE", "MUNICIPAL", .)
replace juz_falla1 = subinstr(juz_falla1, "DE CONOCIMIEENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CIRCUITO DE", "CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "MUNICIAPAL", "MUNICIPAL", .)
replace juz_falla1 = subinstr(juz_falla1, "-", "", .)
replace juz_falla1 = subinstr(juz_falla1, "DE CONOICMIENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "DE CONCOMIENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "DE CONOCIMEIENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CIRUCITO", "CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "FUCION", "", .)
replace juz_falla1 = subinstr(juz_falla1, "PENA ", "PENAL ", .)
replace juz_falla1 = subinstr(juz_falla1, "PENBL", "PENAL", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZGAO", "JUZGADO", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .) 
 replace juz_falla1 = subinstr(juz_falla1, "MANIZALES", "", .)
 replace juz_falla1 = subinstr(juz_falla1, "IBAGUE", "", .)
 replace juz_falla1 = subinstr(juz_falla1, "TUNJA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "PEREIRA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "PALMIRA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "PASTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "BUCARAMANGA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "MEDELLIN", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CALI", "", .)
replace juz_falla1 = subinstr(juz_falla1, "NEIVA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "BUGA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "ARMENIA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "FLORENCIA", "", .)
replace juz_falla1 = subinstr(juz_falla1, "VILLAVICENCIO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "POPAYAN", "", .)
 

replace juz_falla1 = subinstr(juz_falla1, "CONCIMIENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "P.", "PENAL", .) 
replace juz_falla1 = subinstr(juz_falla1, ".", "", .)
replace juz_falla1 = subinstr(juz_falla1, "Â°", "", .)
replace juz_falla1 = subinstr(juz_falla1, "PERNAL", "PENAL", .)
replace juz_falla1 = subinstr(juz_falla1, "BOGOTÃ", "", .)
replace juz_falla1 = subinstr(juz_falla1, "ESPECIALIZADO DE", "ESPECIALIZADO", .)
replace juz_falla1 = subinstr(juz_falla1, "ESP ", "ESPECIALIZADO ", .)
replace juz_falla1 = subinstr(juz_falla1, "DE L ", "DEL ", .)

replace juz_falla1 = subinstr(juz_falla1, "CIRCUTO DE", "CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "CON FUNCIION", "", .)
replace juz_falla1 = subinstr(juz_falla1, "FUNCIION", "", .)
replace juz_falla1 = subinstr(juz_falla1, "CRICUITO", "CIRCUITO", .)
replace juz_falla1 = subinstr(juz_falla1, "PENAL PENAL", "PENAL", .)
replace juz_falla1 = subinstr(juz_falla1, "CONOCIMENTO", "", .)
replace juz_falla1 = subinstr(juz_falla1, "JUZGADP", "JUZGADO", .)
replace juz_falla1 = subinstr(juz_falla1, "CONOCIMEINTO", "", .)

foreach x in  10 11 12 13 14 15 16 17 18 19 20 1 2 3 4 5 6 7 8 9 {
replace juz_falla1 = subinstr(juz_falla1, "JUZGADO`x' ", "JUZGADO `x' ", .)												
	}
	 


replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .)
replace juz_falla1 = subinstr(juz_falla1, "  ", " ", .) 

set more off
foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="JUZGADO `x' PENAL MUNICIPAL" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PENAL MUNICIPAL" & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PENAL MUNICIPAL" & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="`x' PENAL MUNICIPAL" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PENAL MUNICIPAL" & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PENAL MUNICIPAL" & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="`x' PENAL MUNICIPAL " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PENAL MUNICIPAL " & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PENAL MUNICIPAL " & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="JUZGADO `x' PENAL MUNICIPAL " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PENAL MUNICIPAL " & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PENAL MUNICIPAL " & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=314`x' if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO" & judge==. 
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO" & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=314`x' if juz_falla1=="`x' PENAL DEL CIRCUITO" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PENAL DEL CIRCUITO" & judge==. 
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PENAL DEL CIRCUITO" & judge==.
drop temp temp2
}
foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=314`x' if juz_falla1=="`x' PENAL DEL CIRCUITO " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PENAL DEL CIRCUITO " & judge==. 
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PENAL DEL CIRCUITO " & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=314`x' if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO " & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO " & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=317`x' if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO ESPECIALIZADO" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO ESPECIALIZADO" & judge==. 
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO ESPECIALIZADO" & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=317`x' if juz_falla1=="`x' PENAL DEL CIRCUITO ESPECIALIZADO" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PENAL DEL CIRCUITO ESPECIALIZADO" & judge==. 
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PENAL DEL CIRCUITO ESPECIALIZADO" & judge==.
drop temp temp2
}
foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=317`x' if juz_falla1=="`x' PENAL DEL CIRCUITO ESPECIALIZADO " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PENAL DEL CIRCUITO ESPECIALIZADO " & judge==. 
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PENAL DEL CIRCUITO ESPECIALIZADO " & judge==.
drop temp temp2
}
foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=317`x' if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO ESPECIALIZADO " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO ESPECIALIZADO " & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PENAL DEL CIRCUITO ESPECIALIZADO " & judge==.
drop temp temp2
}

	
foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="JUZGADO `x' PROMISCUO MUNICIPAL" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PROMISCUO MUNICIPAL" & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PROMISCUO MUNICIPAL" & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="`x' PROMISCUO MUNICIPAL" & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PROMISCUO MUNICIPAL" & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PROMISCUO MUNICIPAL" & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="`x' PROMISCUO MUNICIPAL " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="`x' PROMISCUO MUNICIPAL " & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="`x' PROMISCUO MUNICIPAL " & judge==.
drop temp temp2
}

foreach  x in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ///
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ///
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 ///
81 82 83 84 85 86 87 88 89 {
gen temp=404`x' if juz_falla1=="JUZGADO `x' PROMISCUO MUNICIPAL " & judge==.
egen temp2 =concat(CODANE temp) if juz_falla1=="JUZGADO `x' PROMISCUO MUNICIPAL " & judge==.
destring temp2, replace
replace judge=temp2 if juz_falla1=="JUZGADO `x' PROMISCUO MUNICIPAL " & judge==.
drop temp temp2
}
	
	save "judge_data.dta", replace
	clear
	

**** Second Page
clear

 set more off
foreach x in bog1 bog2 bog3 bog4 bog5 bog6 bog7 bog8 bog9 bog10 bog11 bog12 bog13 ///
 bog14 bog15 bog16 bog17 bog18 bog19 bog20 bog21 bog22 cali1 cali2 cali4 cali5 ibague1 iba2 iba3 palmira ///
 pasto pereria tunja med2 med1 med3 med4 med5  arm2 arme flor  neiva mani buga  buca1 buca2 villa bar1 bar2 popa {
clear
 import delimited SecondPrisonData`x'.csv
 *drop var_36 var_37 var_38 var_39 var_61 var_62 var_63
 rename var_1 juz_ep
 rename var_2 city
 rename var_3 year_jep
rename var_4 CODANE
rename var_5 corporacion
rename var_6 sala
rename var_7 despacho
rename var_8 year_j
rename var_9 n_radicado
rename var_10 recurso 
rename var_11 lastname
rename var_12 name
rename var_13 cedula
rename var_14 alias
rename var_15 name_parents
rename var_16 place_birth
rename var_17 date_birth
rename var_18 marital_st
rename var_19 education
rename var_20 address
rename var_21 phone
rename var_22 crime
rename var_23 multa
rename var_24 pena_years
rename var_25 pena_months
rename var_26 pena_days
rename var_27 place_prison
rename var_28 city_prison
rename var_29 wanted_others1
rename var_30 wanted_others2
rename var_31 orden_captura
rename var_32 lawyer
rename var_33 t_prof
rename var_34 address_lawyer
rename var_35 phone_lawyer
rename var_36 lawyer2
rename var_37 t_prof2
rename var_38 address_lawyer2
rename var_39 phone_lawyer2
rename var_56 injail
rename var_57 injail_since
rename var_58 orden_capt_vig
rename var_59 home_prison
rename var_60 home_prison_since
rename var_65 libertad_cond
rename var_71 susp_cond_pena
rename var_79 date_acta_comp
rename var_82 per_prueba_days
rename var_83 per_prueba_months
rename var_84 per_prueba_years
rename var_85 injail_other
rename var_86 comment
rename var_89 obs_condena
tostring id, replace 
tostring per_prueba_years, replace
tostring per_prueba_months, replace
tostring per_prueba_days, replace
tostring year_j, replace
tostring pena_years, replace
tostring pena_months, replace
tostring pena_days, replace
destring juz_ep, replace
			destring CODANE, replace
			destring corporacion, replace
			destring sala, replace
			destring despacho, replace
			destring n_radicado, replace
			destring recurso, replace
			gen file=`x'
			tostring file, replace
keep id juz_ep city year_jep CODANE corporacion sala despacho year_j n_radicado recurso lastname ///
name cedula place_birth date_birth crime multa pena_years pena_months pena_days injail injail_since orden_capt_vig ///
home_prison home_prison_since libertad_cond susp_cond_pena date_acta_comp per_prueba_days per_prueba_months per_prueba_years injail_other file 
save "SecondPrisonData`x'.dta", replace
clear
}

clear

foreach x in bog1 bog2 bog3 bog4 bog5 bog6 bog7 bog8 bog9 bog10 bog11 bog12 bog13 ///
 bog14 bog15 bog16 bog17 bog18 bog19 bog20 bog21 bog22 cali1 cali2 cali4 cali5 ibague1 iba2 iba3 palmira ///
 pasto pereria tunja med2 med1 med3 med4 med5  arm2 arme flor  neiva mani buga  buca1 buca2 villa bar1 bar2 popa {
append using "SecondPrisonData`x'.dta"
}
save "Bog2.dta", replace
foreach x in A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Ñ Ãº Ã¡ Ã³ Ã± Ã¹ Ã² - . ( ) a b c d e f g h i j k l m n o p q r s t u v w x y z Ã° __  Ã _  {
replace cedula = subinstr(cedula, "`x'", "", .)
}
replace cedula = subinstr(cedula, "iniudad", "", .)
replace cedula = subinstr(cedula, " ", "", .)
replace cedula = subinstr(cedula, ",", "", .)
destring cedula , replace

foreach var in id year_jep year_j pena_years pena_months pena_days per_prueba_days per_prueba_months per_prueba_years{
foreach x in A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Ñ Ãº Ã¡ Ã³ Ã± Ã¹ Ã² - .  / & ; :  $ ,  * Ã ( ) ndocumentado Ã° __  Ã _ indocumentadass iniudad a b c d e f g h i j k l m n o p q r s t u v w x y z{
replace `var' = subinstr(`var', "`x'", "", .)
}
replace `var' = subinstr(`var', " ", "", .)
destring `var', replace
}
duplicates drop id juz_ep city year_jep CODANE corporacion sala despacho year_j n_radicado recurso lastname name cedula ///
place_birth date_birth crime multa pena_years pena_months pena_days injail injail_since orden_capt_vig home_prison ///
home_prison_since libertad_cond susp_cond_pena date_acta_comp per_prueba_days per_prueba_months per_prueba_years injail_other, force
replace pena_years=0 if pena_years==.
replace pena_months=0 if pena_months==.
replace pena_days=0 if pena_days==.
gen penaym= pena_years*12
gen penadm= pena_days/30
gen penatot_m=penaym+penadm+ pena_months

replace crime=upper(crime)
set more off
replace crime = subinstr(crime, "Ãº", "U", .)
replace crime = subinstr(crime, "Ã¡", "A", .)
replace crime = subinstr(crime, "Ã³", "O", .)
replace crime = subinstr(crime, "Ã±", "Ñ", .)
replace crime = subinstr(crime, "  ", " ", .)
replace crime = subinstr(crime, "Ã¹", "U", .)
replace crime = subinstr(crime, "Ã²", "O", .)
replace crime = subinstr(crime, "  ", " ", .)
replace crime = subinstr(crime, "  ", " ", .)
replace crime = subinstr(crime, "VEHÃ­CULO", "VEHICULO", .)
replace crime = subinstr(crime, "Ã¬", "I", .)
replace crime = subinstr(crime, "DOC.", "DOCUMENTO", .)
replace crime = subinstr(crime, "DOCUMENT.", "DOCUMENTO", .)
replace crime = subinstr(crime, "DOCUM.", "DOCUMENTO", .)
replace crime = subinstr(crime, "TRÃ FICO", "TRAFICO", .)
replace crime = subinstr(crime, "Ã¨", "E", .)
split crime, p(" - ")
foreach x in 1 2 3 4 5 6 7 8 {
gen ccrime`x'=1 if crime1!=""
replace ccrime`x'=0 if crime`x'==""
}
egen ncrime=rowtotal(ccrime1 ccrime2 ccrime3 ccrime4 ccrime5 ccrime6 ccrime7 ccrime8)
drop ccrime*
foreach x in 1 2 3 4 5 6 7 8 {
replace crime`x' = subinstr(crime`x', " -", "", .)
}
do "Crime code.do"
save "sentence_data.dta"
clear

*******************************************************************************

clear
use  "sentence_data.dta"

 tostring CODANE, replace
 destring recurso, replace
 drop if id==""
 destring despacho, replace
 duplicates drop id juz_ep year_jep CODANE corporacion sala despacho year_j n_radicado recurso
 destring n_radicado, replace
replace juz_ep="4" if juz_ep=="0 4"
merge 1:1 id juz_ep year_jep CODANE corporacion sala despacho year_j n_radicado recurso using "judge_data.dta"

keep if _merge==3

gen iddouble=id
destring iddouble, replace
drop if iddouble<10000
drop iddouble
save "j&s.dta", replace

split date_fallo1, p(/)
gen temporal=1 if date_fallo14!="" & date_fallo11==""
replace date_fallo11=date_fallo12 if temporal==1
replace date_fallo12=date_fallo13 if temporal==1
drop date_fallo14
drop temporal
rename date_fallo11 sent_day
rename date_fallo12 sent_month
rename date_fallo13 sent_year

replace sent_year = subinstr(sent_year, " ", "", .)
destring  sent_year, replace
recode sent_year (85=1985) (86=1986) (87=1987) (88=1988) (89=1989) (90=1990) (91=1991) (92=1992) (93=1993) (94=1994) (95=1995) (96=1996) (97=1997) (98=1998) (99=1999) (00=2000) (01=2001) (02=2002) (03=2003) (04=2004) (05=2005) (06=2006) (07=2007) (08=2008) (09=2009) (10=2010) (11=2011) (12=2012) (13=2013) (14=2014) (15=2015) (16=2016) (17=2017)
replace sent_year=. if sent_year<1985 | sent_year>2017
replace sent_month="" if sent_month!="01" & sent_month!="02" & sent_month!="03" &  sent_month!="04" & sent_month!="05" & sent_month!="06" & sent_month!="07" & sent_month!="08" & sent_month!="09" & sent_month!="10" & sent_month!="11" & sent_month!="12" 
destring  sent_month, replace

gen max_sent=36 if year_j<2014
replace max_sent=48 if year_j>=2014 & year_j!=.
gen sent_forjail=1 if penatot_m>max_sent & penatot_m!=.
replace sent_forjail=0 if penatot_m<=max_sent & penatot_m!=.

gen zero="0"
egen tempsala=concat(corporacion zero sala)
egen tempsala2=concat(corp zero sala)
replace salaj=tempsala if salaj=="" & ( corporacion=="31" | corporacion=="40") &  ( sala=="4" | sala=="7" | sala=="89")
replace salaj=tempsala2 if salaj=="" & ( corp=="31" | corp=="40") &  ( sala=="4" | sala=="7" | sala=="89")
drop tempsala tempsala2 
replace salaj="3189" if salaj=="31089"
replace salaj="4089" if salaj=="40089"
gen sentl_valid=0
replace sentl_valid=1 if penatot_m!=. 

tostring id, replace format (%13.0f)
bysort id: gen temp=_n
bysort id: egen ncase=max(temp)
bysort id: egen yfirstcrime=min(year_j)
rename CODANE codane
generate byte non_numeric = indexnot( codane, "0123456789")
drop if non_numeric
destring codane, replace
 merge m:1 codane using "Codigos_distritos_judiciales.dta", gen(mergedist)
  rename codane CODANE
 keep if mergedist==3
 drop judgecod
 rename salaj judgecod
 tab judgecod
replace judgecod="3189" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITODEL" & judgecod==""
replace judgecod="4002" if juz_falla1=="JUZGADO 2 PENAL MUNICIPAL DEPURACION" & judgecod==""
replace judgecod="4004" if juz_falla1=="1AJUZGADO1PENALMUNICIPAL" & judgecod==""
replace judgecod="4090" if juz_falla1=="JUZGADO 1 PROMISCUO MUNICIPAL DESCONGESTION" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO2PENALDELCIRCUITOH" & judgecod==""
replace judgecod="3189" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOPTORICO" & judgecod==""
replace judgecod="4004" if juz_falla1=="JUZGADO22MUNICIPAL" & judgecod==""
replace judgecod="4002" if juz_falla1=="JUZGADO 1 PENAL MUNICIPAL DEPURACION" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO1PENALDELCIRCUITOANJOSDEL" & judgecod==""
replace judgecod="3190" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITODESCONGESTION" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO 1 PENAL DEL CIRCUITO" & judgecod==""
replace judgecod="3104" if juz_falla1=="J4PENALDELCIRCUITOPORREDISTRIBUCION8" & judgecod==""
replace judgecod="3104" if juz_falla1=="J6PENALDELCIRCUITOPORREDISTRIBUCION8" & judgecod==""
replace judgecod="3104" if juz_falla1=="J3PENALDELCIRCUITOPORREDISTRIBUCION8" & judgecod==""
replace judgecod="3104" if juz_falla1=="PRIMERAJUZGADOCTO" & judgecod==""
replace judgecod="4005" if juz_falla1=="1 PENAL MUNICIPAL DESCONGESTION" & judgecod==""
replace judgecod="3189" if juz_falla1=="JUZGADO2PROMISCUODELCIRCUITOH" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO1PENALDELCIRCUITOLAUNIN" & judgecod==""
replace judgecod="4002" if juz_falla1=="JUZGADO3PENALMUNICIPALDEPURACION" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADOPRIEMROPENALDELCIRCUITO" & judgecod==""
replace judgecod="4089" if juz_falla1=="JUZGADO1PROMMUNICIPAL" & judgecod==""
replace judgecod="4002" if juz_falla1=="JUZGADO2PENALMUNICIPALDEPURACION" & judgecod==""
replace judgecod="3104" if juz_falla1=="J5PENALDELCIRCUITOPORREDISTRIBUCION8" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO1PENALDELCIRCUITOODELCIRCUITOPTORICO" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOADO" & judgecod==""
replace judgecod="4004" if juz_falla1=="1AJUZGADO PENAL MUNICIPAL" & judgecod==""
replace judgecod="4002" if juz_falla1=="JUZGADO 1 PENAL MUNICIPAL DEPURACION" & judgecod==""
replace judgecod="4089" if juz_falla1=="JUZGADO1PROMISCUOMUNICIPALD11LLO" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALESPECIALIZADO" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO2PENALDELCIRCUITODDAS" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADODE" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADOSEGUNTOPENALDELCIRCUITOESPECIALIZADO" & judgecod==""
replace judgecod="3104" if juz_falla1=="J1PENALDELCIRCUITOPORREDISTRIBUCION8" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOCTOESPECIALIZADO" & judgecod==""
replace judgecod="3189" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOINRIDAGUAINA" & judgecod==""
replace judgecod="4004" if juz_falla1=="JUZGADO1MUNICIPALTIMAN" & judgecod==""
replace judgecod="3104" if juz_falla1=="J2PENALDELCIRCUITOPORREDISTRIBUCION8" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADOEGUNDOPENALDELCIRCUITO" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO1PENALDELCIRCUITOODELCIRCUITO" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO 2 PENAL DEL CIRCUITO ESPENAL DEL CIRCUITO" & judgecod==""
replace judgecod="3104" if juz_falla1=="J 4 PENAL DEL CIRCUITO POR REDISTRIBUCION 8" & judgecod==""
replace judgecod="4004" if juz_falla1=="JUZGADO1PENALMUNICIPALPENAL" & judgecod==""
replace judgecod="3189" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOCAQUET" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADOTERECROPENALDELCIRCUITO" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOC" & judgecod==""
replace judgecod="4004" if juz_falla1=="JUZGADOPRIEMROPENALMUNICIPAL" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOEREIRA" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO1PENALDELCIRCUITOFUN" & judgecod==""
replace judgecod="4089" if juz_falla1=="JUZGADO1PROMISCUOMUNICIPALARZU" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO2PENALDELCIRCUITOC" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECILDO" & judgecod==""
replace judgecod="3189" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOH" & judgecod==""
replace judgecod="3189" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOCHA" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO3PENALDELCIRCUITOQ" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOITINERE" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO1PENALCTO" & judgecod==""
replace judgecod="3107" if juz_falla1=="J2ESPECIALIZADO" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO4PENALDELCIRCUITOPENAL" & judgecod==""
replace judgecod="3107" if juz_falla1=="JUZGADO2PENALDELCIRCUITOESPECIALZADO" & judgecod==""
replace judgecod="3104" if juz_falla1=="JUZGADO4PENALDELCIRCUITOQ" & judgecod==""

replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITODEL" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO 2 PENAL MUNICIPAL DEPURACION" & despacho_n==""
replace despacho_n="1" if juz_falla1=="1AJUZGADO1PENALMUNICIPAL" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO 1 PROMISCUO MUNICIPAL DESCONGESTION" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO2PENALDELCIRCUITOH" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOPTORICO" & despacho_n==""
replace despacho_n="22" if juz_falla1=="JUZGADO22MUNICIPAL" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO 1 PENAL MUNICIPAL DEPURACION" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOANJOSDEL" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITODESCONGESTION" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO 1 PENAL DEL CIRCUITO" & despacho_n==""
replace despacho_n="4" if juz_falla1=="J4PENALDELCIRCUITOPORREDISTRIBUCION8" & despacho_n==""
replace despacho_n="6" if juz_falla1=="J6PENALDELCIRCUITOPORREDISTRIBUCION8" & despacho_n==""
replace despacho_n="3" if juz_falla1=="J3PENALDELCIRCUITOPORREDISTRIBUCION8" & despacho_n==""
replace despacho_n="1" if juz_falla1=="PRIMERAJUZGADOCTO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="1 PENAL MUNICIPAL DESCONGESTION" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO2PROMISCUODELCIRCUITOH" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOLAUNIN" & despacho_n==""
replace despacho_n="3" if juz_falla1=="JUZGADO3PENALMUNICIPALDEPURACION" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADOPRIEMROPENALDELCIRCUITO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMMUNICIPAL" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO2PENALMUNICIPALDEPURACION" & despacho_n==""
replace despacho_n="5" if juz_falla1=="J5PENALDELCIRCUITOPORREDISTRIBUCION8" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOODELCIRCUITOPTORICO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOADO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="1AJUZGADO PENAL MUNICIPAL" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO 1 PENAL MUNICIPAL DEPURACION" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUOMUNICIPALD11LLO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALESPECIALIZADO" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO2PENALDELCIRCUITODDAS" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADODE" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADOSEGUNTOPENALDELCIRCUITOESPECIALIZADO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="J1PENALDELCIRCUITOPORREDISTRIBUCION8" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOCTOESPECIALIZADO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOINRIDAGUAINA" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1MUNICIPALTIMAN" & despacho_n==""
replace despacho_n="2" if juz_falla1=="J2PENALDELCIRCUITOPORREDISTRIBUCION8" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADOEGUNDOPENALDELCIRCUITO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOODELCIRCUITO" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO 2 PENAL DEL CIRCUITO ESPENAL DEL CIRCUITO" & despacho_n==""
replace despacho_n="4" if juz_falla1=="J 4 PENAL DEL CIRCUITO POR REDISTRIBUCION 8" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALMUNICIPALPENAL" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOCAQUET" & despacho_n==""
replace despacho_n="3" if juz_falla1=="JUZGADOTERECROPENALDELCIRCUITO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOC" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADOPRIEMROPENALMUNICIPAL" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOEREIRA" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOFUN" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUOMUNICIPALARZU" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO2PENALDELCIRCUITOC" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECILDO" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOH" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PROMISCUODELCIRCUITOCHA" & despacho_n==""
replace despacho_n="3" if juz_falla1=="JUZGADO3PENALDELCIRCUITOQ" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALDELCIRCUITOESPECIALIZADOITINERE" & despacho_n==""
replace despacho_n="1" if juz_falla1=="JUZGADO1PENALCTO" & despacho_n==""
replace despacho_n="2" if juz_falla1=="J2ESPECIALIZADO" & despacho_n==""
replace despacho_n="4" if juz_falla1=="JUZGADO4PENALDELCIRCUITOPENAL" & despacho_n==""
replace despacho_n="2" if juz_falla1=="JUZGADO2PENALDELCIRCUITOESPECIALZADO" & despacho_n==""
replace despacho_n="4" if juz_falla1=="JUZGADO4PENALDELCIRCUITOQ" & despacho_n==""

 gen rand_level=cod_m if judgecod=="4004"  | judgecod=="4089" | judgecod=="4005"  | judgecod=="4002"   | judgecod=="4001" | judgecod=="4090"     
 replace rand_level=cod_circ if  judgecod=="3104" | judgecod=="3189" | judgecod=="3106" | judgecod=="3105"  | judgecod=="3103"   | judgecod=="3102"   | judgecod=="3190"  
 replace rand_level=cod_dist if  judgecod=="3107" |  judgecod=="3111" |  judgecod=="3108"  |  judgecod=="3109"
 drop if year_j==.
 egen r2=group(rand_level year_j)
drop if judgecod=="" & r2==.
drop if r2==.
keep if despacho_n!="" & judgecod!="" &  rand_level!=.
egen judge_name_merge=concat(rand_level judgecod  despacho_n sent_year sent_month)
drop if sent_year<2004
keep if year_j>2004
merge m:1 judge_name_merge using "judge_roaster.dta", gen(merge_name)
egen judge_merge=concat( rand_level judgecod  despacho_n jname_id ) if despacho_n!="" & judgecod!="" &  rand_level!=. & merge_name==3
bysort judge_merge year_j: egen temp10=count(id)
bysort judge_merge year_j: egen ncasesa=max(temp10)
drop temp10
keep if ncasesa>15  & ncasesa!=.

destring despacho_n, gen(desp)
bysort rand_level desp: gen temp9=_n
bysort rand_level : egen num_j_pru=count(temp9) if temp9==1
tab num_j_pru
bysort rand_level : egen num_jueces=max(num_j_pru)
keep if num_jueces>1

merge m:1 judge_merge using  "spoa" , gen(magspoa)
keep if magspoa==3
egen r2b=group(rand_level year_j  ) if rand_level!=. & year_j!=.  
save "j&s.dta", replace
clear

********************************************************************************

clear
 use "sisbensimat.dta"
 rename idglobal20141126 idglobal
rename sisben_documento id
rename CODANE sisben_codane
foreach x in # ߠ A � a b c d e f g h i j k l m n o p ∟ ñ q r s t u v w x y z , {
replace id = subinstr(id, "`x'", "", .)
}
replace id = subinstr(id, " ", "", .)
foreach x in � , {
replace id = subinstr(id, "`x'", "", .)
}
replace id = subinstr(id, "]", "", .)
replace id = subinstr(id, "_", "", .)
replace id = subinstr(id, "�", "", .)
replace id = subinstr(id, "O", "0", .)
merge m:1 id using "j&s.dta", gen(merge_fam_crime)
save "data.dta", replace
clear
********************************************************************************
